const express = require('express')
const router = express.Router()
const db = require('../mysql')
const { route } = require('./user')

// TODO: 查询sql
// 查询所有单词(默认单词+用户单词) 参数：user_phone,start 分页
let getlist = ' SELECT * FROM words where user_phone = 000 or user_phone= ? LIMIT ?,?'
// 查询总数
let allCount = ' SELECT COUNT(*) as total FROM words WHERE user_phone = 000 or user_phone=?'
// 删除单词 (person)
let delsql = 'DELETE FROM words WHERE word_id = ? and  user_phone = ?'
// 添加单词
let addsql = 'INSERT INTO `word_book`.`words` (`words`, `spell`, `meaning`, `user_phone`) VALUES (?,?,?,?)'
// 更新单词
let updsql = 'UPDATE words SET words=?,spell=?,meaning=? WHERE word_id=?'
// 查询单个单词
let lonesql = 'SELECT * FROM words WHERE word_id = ?'
// TODO:获取单词列表+分页，总数，昵称 (user_phone,start)
router.post('/wordlist', (req, res) => {
  // 返回对象
  let backObj = {}
  // 查询数据
  const user_phone = req.body.user_phone
  const startno = (parseInt(req.body.start)-1 )*10
  const endno = startno+10
  // 查询单词数据列表（分页）
  db.query(getlist,[user_phone,startno,endno], (err, result) => {
    if (err) return err.message
    backObj.result = result
      // 获取单词数量
  db.query(allCount, user_phone, (err, result) => {
    if (err) return err.message
    backObj.total = result[0].total
    res.send(backObj)
  })
  })

 
})
 
// TODO:删除单词(id,user_phone)
router.post('/delword', (req, res) => {
  console.log('删除'+req.body.id)
  let ids = req.body.id
  let user_phone = req.body.user_phone
  db.query(delsql, [ids,user_phone], (err, result) => {
    if (err) return err.message
    
   if (result.affectedRows === 1) {
      res.send({message:'删除成功',status:200})
   } else {
     res.send({
       message: '删除失败',
       status:303
     })
   }
 })
})
// TODO:添加单词(words,spell,meaning,user_phone)
router.post('/addword', (req, res) => {
  let datas = {
    words: req.body.words, 
    spell: req.body.spell,
    meaning:req.body.meaning,
    user_phone:req.body.user_phone
}
  db.query(addsql, [datas.words,datas.spell,datas.meaning,datas.user_phone], (err, result) => {
    if (err) return err.message
   if (result.affectedRows === 1) {
      res.send({messag:'插入成功',status:200})
   } else {
     res.send({
       message: '插入失败',
       status:303
     })
   }
 })
})
// TODO:查询单个单词
router.get('/loneword', (req, res) => {
  let ids =parseInt( req.query.id)
  db.query(lonesql, ids, (err, result) => {
    if (err) return err.messag
    if (result.length > 0) {
      res.send({
        result: result,
        status:200
      })
    }
  })
})
// TODO:更新(words,spell,meaning,id)
router.post('/updword', (req, res) => {
  let datas = {
    words: req.body.words, 
    spell: req.body.spell,
    meaning:req.body.meaning,
    word_id:parseInt(req.body. word_id)
}
  db.query(updsql, [datas.words,datas.spell,datas.meaning,datas.word_id], (err, result) => {
    if (err) return err.message
   if (result.affectedRows === 1) {
      res.send({messag:'更新成功',status:200})
   } else {
     res.send({
       message: '更新失败',
       status:303
     })
   }
 })
})
module.exports= router